package com.common.www.service.tabulation;


import org.springframework.boot.base.jpa.service.BaseLogicService;
import com.common.www.bean.entity.tabulation.InnerBidBaseTabulation;
import com.common.www.repository.tabulation.InnerBidBaseTabulationRepository;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.math.BigDecimal;
import java.util.*;
import java.util.stream.Collectors;

@Service
@Transactional
public class InnerBidBaseTabulationService extends BaseLogicService<InnerBidBaseTabulation, Long> {
    @Autowired
    private InnerBidBaseTabulationRepository repository;

    @Override
    public InnerBidBaseTabulationRepository getRepository() {
        return repository;
    }


    /**
     * 导入内线标底清单
     *
     * @param workbook
     * @return
     * @throws Exception
     */
    public List<InnerBidBaseTabulation> importSimulate(Workbook workbook) throws Exception {
        Sheet sheet = null;
        if (workbook.getNumberOfSheets() > 0) {
            sheet = workbook.getSheetAt(0);
        }
        if (sheet == null) {
            throw new Exception("未找到导入数据,请检查文档");
        }
        List<InnerBidBaseTabulation> list = new ArrayList<>();

        BigDecimal qiYeCostFillIn = BigDecimal.ZERO;
        BigDecimal shuiJinFillIn = BigDecimal.ZERO;
        for (int rowIndex = 5; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
//            System.out.println("第 " + rowIndex + " 行");
            String r0 = null;
            String r1 = null;
            String r2 = null;
            String r3 = null;
            String r4 = null;
            String r5 = null;
            String r6 = null;
            String r7 = null;
            String r8 = null;
            String r9 = null;
            String r10 = null;
            String r11 = null;
            String r12 = null;
            String r13 = null;
            String r14 = null;
            String r15 = null;
            String r16 = null;
            String r17 = null;
            String r18 = null;
            String r19 = null;
            String r20 = null;

            Row row = sheet.getRow(rowIndex);
            if (row == null) {
                break;
            }
            Iterator<Cell> cellIterator = row.cellIterator();
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                cell.setCellType(CellType.STRING);
                String value = cell.getStringCellValue();

                if (cell.getColumnIndex() == 0)
                    r0 = value.trim();
                if (cell.getColumnIndex() == 1)
                    r1 = value.trim();
                if (cell.getColumnIndex() == 2)
                    r2 = value.trim();
                if (cell.getColumnIndex() == 3)
                    r3 = value.trim();
                if (cell.getColumnIndex() == 4)
                    r4 = value.trim();
                if (cell.getColumnIndex() == 5)
                    r5 = value.trim();
                if (cell.getColumnIndex() == 6)
                    r6 = value.trim();
                if (cell.getColumnIndex() == 7)
                    r7 = value.trim();
                if (cell.getColumnIndex() == 8)
                    r8 = value.trim();
                if (cell.getColumnIndex() == 9)
                    r9 = value.trim();
                if (cell.getColumnIndex() == 10)
                    r10 = value.trim();
                if (cell.getColumnIndex() == 11)
                    r11 = value.trim();
                if (cell.getColumnIndex() == 12)
                    r12 = value.trim();
                if (cell.getColumnIndex() == 13)
                    r13 = value.trim();
                if (cell.getColumnIndex() == 14)
                    r14 = value.trim();
                if (cell.getColumnIndex() == 15)
                    r15 = value.trim();
                if (cell.getColumnIndex() == 16)
                    r16 = value.trim();
                if (cell.getColumnIndex() == 17)
                    r17 = value.trim();
                if (cell.getColumnIndex() == 18)
                    r18 = value.trim();
                if (cell.getColumnIndex() == 19)
                    r19 = value.trim();
                if (cell.getColumnIndex() == 20)
                    r20 = value.trim();

            }
            if (rowIndex == 5){
                qiYeCostFillIn = (r16 != null && !r16.isEmpty()) ? BigDecimal.valueOf(Double.parseDouble(r16)) : null;
                shuiJinFillIn = (r17 != null && !r17.isEmpty()) ? BigDecimal.valueOf(Double.parseDouble(r17)) : null;
            }else {
                InnerBidBaseTabulation io = new InnerBidBaseTabulation();
                io.setCodeNumber(r0);
                io.setName(r1);
                String fenLeiStr = getFenLei(r2);
                if (fenLeiStr != null) {
                    io.setFenLei(Integer.valueOf(fenLeiStr));
                } else {
                    io.setFenLei(null); // 或者设置一个默认值
                }
                io.setClassificationCode(r3);
                io.setModelCode(r4);
                io.setGongZuoNeiRong(r5);
                io.setUnit(r6);
                io.setGongChengLiang(r7 != null && !r7.isEmpty() ? Double.parseDouble(r7) : null);
                io.setZhuZhai(r8 != null && !r8.isEmpty() ? Double.parseDouble(r8) : null);
                io.setFeiJu(r9 != null && !r9.isEmpty() ? Double.parseDouble(r9) : null);
                io.setShangYe(r10 != null && !r10.isEmpty() ? Double.parseDouble(r10) : null);
                io.setChongDianZhuang(r11 != null && !r11.isEmpty() ? Double.parseDouble(r11) : null);
                io.setRenGongCost(r12 != null && !r12.isEmpty() ? BigDecimal.valueOf(Double.parseDouble(r12)) : null);
                io.setZhuCaiCost(r13 != null && !r13.isEmpty() ? BigDecimal.valueOf(Double.parseDouble(r13)) : null);
                io.setFuCaiCost(r14 != null && !r14.isEmpty() ? BigDecimal.valueOf(Double.parseDouble(r14)) : null);
                io.setJiXieCost(r15 != null && !r15.isEmpty() ? BigDecimal.valueOf(Double.parseDouble(r15)) : null);
                io.setQiYeCost(r16 != null && !r16.isEmpty() ? BigDecimal.valueOf(Double.parseDouble(r16)) : null);
                io.setShuiJinOriginal(r17 != null && !r17.isEmpty() ? BigDecimal.valueOf(Double.parseDouble(r17)) : null);
                io.setComprehensivePriceOriginal(r18 != null && !r18.isEmpty() ? BigDecimal.valueOf(Double.parseDouble(r18)) : null);
                io.setTotalPriceOriginal(r19 != null && !r19.isEmpty() ? BigDecimal.valueOf(Double.parseDouble(r19)) : null);
                io.setRemarks(r20);
                io.setQiYeCostFillIn(qiYeCostFillIn);
                io.setShuiJinFillIn(shuiJinFillIn);
                list.add(io);
            }
        }

        return list;
    }


    /**
     * 根据名称返回值
     * @param r
     * @return
     */
    public String getFenLei(String r) {
        if (StringUtils.isEmpty(r)) {
            return null;
        }else {
            switch (r) {
                case "直流屏":
                    return "1";
                case "高压开关柜":
                    return "2";
                case "低压开关柜":
                    return "3";
                case "母线槽":
                    return "4";
                case "变压器":
                    return "5";
                case "10kv高压电缆":
                    return "6";
                case "架空线":
                    return "7";
                case "低压电缆":
                    return "8";
                case "预分支电缆":
                    return "9";
                case "电表箱":
                    return "10";
                case "配电箱":
                    return "11";
                case "电表":
                    return "12";
                case "高压桥架":
                    return "13";
                case "调试":
                    return "14";
                case "电缆通道":
                    return "15";
                case "电杆":
                    return "16";
                case "手续费":
                    return "17";
                case "措施费":
                    return "18";
                case "其他":
                    return "19";
                default:
                    return "0";
            }
        }
    }

    /**
     * 创建单元格
     * @param row
     * @param index
     * @param style
     * @return
     */
    public static Cell createCellAddStype(Row row, Integer index, CellStyle style) {
        Cell hssfCell = row.createCell(index);
        hssfCell.setCellStyle(style);
        return hssfCell;
    }

    /**
     * 导出清单
     * @param projectId
     * @param bidType
     * @param fileName
     * @return
     */
    public XSSFWorkbook exportQd(Long projectId,Integer bidType,String fileName) {
        HashMap hashMap = new HashMap();
        hashMap.put("flag", 1);
        hashMap.put("project.id", projectId);
        hashMap.put("bidType", bidType);
        List<InnerBidBaseTabulation> items = getListLogic(hashMap, null);
        BigDecimal qiYeCostFillIn = BigDecimal.ZERO;
        BigDecimal shuiJinFillIn = BigDecimal.ZERO;
        if (items.size() > 0){
            qiYeCostFillIn = items.get(0).getQiYeCostFillIn();
            shuiJinFillIn = items.get(0).getShuiJinFillIn();
        }
        items.sort(Comparator.comparing(InnerBidBaseTabulation::getNameType1));

        Map<String, List<InnerBidBaseTabulation>> nameType1List = items.stream()
                .collect(Collectors.groupingBy(InnerBidBaseTabulation::getNameType1));

        XSSFWorkbook workbook = new XSSFWorkbook();
        String sheetName = fileName + "内线清单";
        Sheet sheet = workbook.createSheet(sheetName);
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER); // 居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 居中
        // 创建日期格式
        CellStyle cellDateStyle = workbook.createCellStyle();
        CreationHelper createHelper = workbook.getCreationHelper();
        cellDateStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-mm-dd hh:mm:ss"));
        Row row0 = sheet.createRow(0);
        createCellAddStype(row0, 0, cellStyle).setCellValue("高低压供配电内线工程量清单明细");
        Row row1 = sheet.createRow(1);
        createCellAddStype(row1, 0, cellStyle).setCellValue("工程名称:");
        createCellAddStype(row1, 1, cellStyle).setCellValue(items.get(0).getProject().getName()+"高低压供配电工程");

        CellRangeAddress titleRegion = new CellRangeAddress(0, 0, 0, 19);// 下标从0开始 起始行号，终止行号， 起始列号，终
        sheet.addMergedRegionUnsafe(titleRegion);
        //边框
        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyle.setBorderTop(BorderStyle.THIN);//上边框
        cellStyle.setBorderRight(BorderStyle.THIN);//右边框
        cellDateStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellDateStyle.setBorderLeft(BorderStyle.THIN);//左边框
        cellDateStyle.setBorderTop(BorderStyle.THIN);//上边框
        cellDateStyle.setBorderRight(BorderStyle.THIN);//右边框

        Row row2 = sheet.createRow(2);
        createCellAddStype(row2, 0, cellStyle).setCellValue("序号");
        createCellAddStype(row2, 1, cellStyle).setCellValue("项目名称");
        createCellAddStype(row2, 2, cellStyle).setCellValue("分类");
        createCellAddStype(row2, 3, cellStyle).setCellValue("编号");
        createCellAddStype(row2, 4, cellStyle).setCellValue("型号");
        createCellAddStype(row2, 5, cellStyle).setCellValue("工作内容");
        createCellAddStype(row2, 6, cellStyle).setCellValue("单位");
        createCellAddStype(row2, 7, cellStyle).setCellValue("工程量");
        createCellAddStype(row2, 8, cellStyle).setCellValue("住宅");
        createCellAddStype(row2, 9, cellStyle).setCellValue("非居");
        createCellAddStype(row2, 10, cellStyle).setCellValue("商业");
        createCellAddStype(row2, 11, cellStyle).setCellValue("充电桩");
        createCellAddStype(row2, 12, cellStyle).setCellValue("综合单价组成明细");
        createCellAddStype(row2, 13, cellStyle).setCellValue("综合单价组成明细");
        createCellAddStype(row2, 14, cellStyle).setCellValue("综合单价组成明细");
        createCellAddStype(row2, 15, cellStyle).setCellValue("综合单价组成明细");
        createCellAddStype(row2, 16, cellStyle).setCellValue("综合单价组成明细");
        createCellAddStype(row2, 17, cellStyle).setCellValue("综合单价组成明细");
        createCellAddStype(row2, 18, cellStyle).setCellValue("综合单价");
        createCellAddStype(row2, 19, cellStyle).setCellValue("合价");
        Row row3 = sheet.createRow(3);
        createCellAddStype(row3, 0, cellStyle).setCellValue("序号");
        createCellAddStype(row3, 1, cellStyle).setCellValue("项目名称");
        createCellAddStype(row3, 2, cellStyle).setCellValue("分类");
        createCellAddStype(row3, 3, cellStyle).setCellValue("编号");
        createCellAddStype(row3, 4, cellStyle).setCellValue("型号");
        createCellAddStype(row3, 5, cellStyle).setCellValue("工作内容");
        createCellAddStype(row3, 6, cellStyle).setCellValue("单位");
        createCellAddStype(row3, 7, cellStyle).setCellValue("工程量");
        createCellAddStype(row3, 8, cellStyle).setCellValue("住宅");
        createCellAddStype(row3, 9, cellStyle).setCellValue("非居");
        createCellAddStype(row3, 10, cellStyle).setCellValue("商业");
        createCellAddStype(row3, 11, cellStyle).setCellValue("充电桩");
        createCellAddStype(row3, 12, cellStyle).setCellValue("人工费");
        createCellAddStype(row3, 13, cellStyle).setCellValue("主材费(含损耗费)");
        createCellAddStype(row3, 14, cellStyle).setCellValue("辅材及其他材料费");
        createCellAddStype(row3, 15, cellStyle).setCellValue("机械费");
        createCellAddStype(row3, 16, cellStyle).setCellValue("企业管理费、利润、规费等");
        createCellAddStype(row3, 17, cellStyle).setCellValue("税金");
        createCellAddStype(row3, 18, cellStyle).setCellValue("综合单价");
        createCellAddStype(row3, 19, cellStyle).setCellValue("合价");

        Row row4 = sheet.createRow(4);
        createCellAddStype(row4, 0, cellStyle).setCellValue("序号");
        createCellAddStype(row4, 1, cellStyle).setCellValue("项目名称");
        createCellAddStype(row4, 2, cellStyle).setCellValue("分类");
        createCellAddStype(row4, 3, cellStyle).setCellValue("编号");
        createCellAddStype(row4, 4, cellStyle).setCellValue("型号");
        createCellAddStype(row4, 5, cellStyle).setCellValue("工作内容");
        createCellAddStype(row4, 6, cellStyle).setCellValue("单位");
        createCellAddStype(row4, 7, cellStyle).setCellValue("工程量");
        createCellAddStype(row4, 8, cellStyle).setCellValue("住宅");
        createCellAddStype(row4, 9, cellStyle).setCellValue("非居");
        createCellAddStype(row4, 10, cellStyle).setCellValue("商业");
        createCellAddStype(row4, 11, cellStyle).setCellValue("充电桩");
        createCellAddStype(row4, 12, cellStyle).setCellValue("a");
        createCellAddStype(row4, 13, cellStyle).setCellValue("b");
        createCellAddStype(row4, 14, cellStyle).setCellValue("c");
        createCellAddStype(row4, 15, cellStyle).setCellValue("d");
        createCellAddStype(row4, 16, cellStyle).setCellValue("e=(a+b+c+d)*Ⓐ%");
        createCellAddStype(row4, 17, cellStyle).setCellValue("f=(a+b+c+d+e)*Ⓑ%");
        createCellAddStype(row4, 18, cellStyle).setCellValue("综合单价");
        createCellAddStype(row4, 19, cellStyle).setCellValue("合价");

        Row row5 = sheet.createRow(5);
        createCellAddStype(row5, 0, cellStyle).setCellValue("序号");
        createCellAddStype(row5, 1, cellStyle).setCellValue("项目名称");
        createCellAddStype(row5, 2, cellStyle).setCellValue("分类");
        createCellAddStype(row5, 3, cellStyle).setCellValue("编号");
        createCellAddStype(row5, 4, cellStyle).setCellValue("型号");
        createCellAddStype(row5, 5, cellStyle).setCellValue("工作内容");
        createCellAddStype(row5, 6, cellStyle).setCellValue("单位");
        createCellAddStype(row5, 7, cellStyle).setCellValue("工程量");
        createCellAddStype(row5, 8, cellStyle).setCellValue("住宅");
        createCellAddStype(row5, 9, cellStyle).setCellValue("非居");
        createCellAddStype(row5, 10, cellStyle).setCellValue("商业");
        createCellAddStype(row5, 11, cellStyle).setCellValue("充电桩");
        createCellAddStype(row5, 12, cellStyle).setCellValue("a");
        createCellAddStype(row5, 13, cellStyle).setCellValue("b");
        createCellAddStype(row5, 14, cellStyle).setCellValue("c");
        createCellAddStype(row5, 15, cellStyle).setCellValue("d");
        createCellAddStype(row5, 16, cellStyle).setCellValue(qiYeCostFillIn.toString());
        createCellAddStype(row5, 17, cellStyle).setCellValue(shuiJinFillIn.toString());
        createCellAddStype(row5, 18, cellStyle).setCellValue("综合单价");
        createCellAddStype(row5, 19, cellStyle).setCellValue("合价");

        CellRangeAddress cra1 = new CellRangeAddress(2, 5, 0, 0);
        CellRangeAddress cra2 = new CellRangeAddress(2, 5,1, 1);
        CellRangeAddress cra3 = new CellRangeAddress(2, 5, 2, 2);
        CellRangeAddress cra4 = new CellRangeAddress(2, 5, 3, 3);
        CellRangeAddress cra5 = new CellRangeAddress(2, 5, 4, 4);
        CellRangeAddress cra6 = new CellRangeAddress(2, 5, 5, 5);
        CellRangeAddress cra7 = new CellRangeAddress(2, 5,6, 6);
        CellRangeAddress cra8 = new CellRangeAddress(2, 5,7, 7);
        CellRangeAddress cra9 = new CellRangeAddress(2, 5, 8, 8);
        CellRangeAddress cra10 = new CellRangeAddress(2, 5,9, 9);
        CellRangeAddress cra11 = new CellRangeAddress(2, 5,10, 10);
        CellRangeAddress cra12 = new CellRangeAddress(2, 5,11, 11);
        CellRangeAddress cra13 = new CellRangeAddress(2, 2, 12, 17);
        CellRangeAddress cra14 = new CellRangeAddress(2, 5, 18, 18);
        CellRangeAddress cra15 = new CellRangeAddress(2, 5, 19, 19);
        CellRangeAddress cra16 = new CellRangeAddress(4, 5, 12, 12);
        CellRangeAddress cra17 = new CellRangeAddress(4, 5, 13, 13);
        CellRangeAddress cra18 = new CellRangeAddress(4, 5, 14, 14);
        CellRangeAddress cra19 = new CellRangeAddress(4, 5, 15, 15);

        //在sheet里增加合并单元格
        sheet.addMergedRegion(cra1);
        sheet.addMergedRegion(cra2);
        sheet.addMergedRegion(cra3);
        sheet.addMergedRegion(cra4);
        sheet.addMergedRegion(cra5);
        sheet.addMergedRegion(cra6);
        sheet.addMergedRegion(cra7);
        sheet.addMergedRegion(cra8);
        sheet.addMergedRegion(cra9);
        sheet.addMergedRegion(cra10);
        sheet.addMergedRegion(cra11);
        sheet.addMergedRegion(cra12);
        sheet.addMergedRegion(cra13);
        sheet.addMergedRegion(cra14);
        sheet.addMergedRegion(cra15);
        sheet.addMergedRegion(cra16);
        sheet.addMergedRegion(cra17);
        sheet.addMergedRegion(cra18);
        sheet.addMergedRegion(cra19);

        Integer rowIndex = 6;
        for (Map.Entry<String, List<InnerBidBaseTabulation>> itemObj : nameType1List.entrySet()) {
            String nameType1 = itemObj.getKey();
            List<InnerBidBaseTabulation> value = itemObj.getValue();
            InnerBidBaseTabulation newT = calculationSubtotal(value);
            Row row6 = sheet.createRow(rowIndex);
            createCellAddStype(row6, 0, cellStyle).setCellValue("");
            createCellAddStype(row6, 1, cellStyle).setCellValue(nameType1);
            createCellAddStype(row6, 2, cellStyle).setCellValue("");
            createCellAddStype(row6, 3, cellStyle).setCellValue("");
            createCellAddStype(row6, 4, cellStyle).setCellValue("");
            createCellAddStype(row6, 5, cellStyle).setCellValue("");
            createCellAddStype(row6, 6, cellStyle).setCellValue("");
            createCellAddStype(row6, 7, cellStyle).setCellValue("");
            createCellAddStype(row6, 8, cellStyle).setCellValue("");
            createCellAddStype(row6, 9, cellStyle).setCellValue("");
            createCellAddStype(row6, 10, cellStyle).setCellValue("");
            createCellAddStype(row6, 11, cellStyle).setCellValue("");
            createCellAddStype(row6, 12, cellStyle).setCellValue(newT.getRenGongCost() != null ? newT.getRenGongCost().doubleValue() : 0);
            createCellAddStype(row6, 13, cellStyle).setCellValue(newT.getZhuCaiCost() != null ? newT.getZhuCaiCost().doubleValue() : 0);
            createCellAddStype(row6, 14, cellStyle).setCellValue(newT.getFuCaiCost() != null ? newT.getFuCaiCost().doubleValue() : 0);
            createCellAddStype(row6, 15, cellStyle).setCellValue(newT.getJiXieCost() != null ? newT.getJiXieCost().doubleValue() : 0);
            createCellAddStype(row6, 16, cellStyle).setCellValue(newT.getQiYeCost() != null ? newT.getQiYeCost().doubleValue() : 0);
            createCellAddStype(row6, 17, cellStyle).setCellValue(newT.getShuiJin() != null ? newT.getShuiJin().doubleValue() : 0);
            createCellAddStype(row6, 18, cellStyle).setCellValue("");
            createCellAddStype(row6, 19, cellStyle).setCellValue(newT.getTotalPrice() != null ? newT.getTotalPrice().doubleValue() : 0);

            for (InnerBidBaseTabulation item : value){
                rowIndex++;
                Row row = sheet.createRow(rowIndex);
                createCellAddStype(row, 0, cellStyle).setCellValue(item.getCodeNumber());
                createCellAddStype(row, 1, cellStyle).setCellValue(item.getName());
                createCellAddStype(row, 2, cellStyle).setCellValue(getFenLeiName(item.getFenLei()));
                createCellAddStype(row, 3, cellStyle).setCellValue(item.getClassificationCode());
                createCellAddStype(row, 4, cellStyle).setCellValue(item.getModelCode());
                createCellAddStype(row, 5, cellStyle).setCellValue(item.getGongZuoNeiRong());
                createCellAddStype(row, 6, cellStyle).setCellValue(item.getUnit() != null ? item.getUnit() : "");
                createCellAddStype(row, 7, cellStyle).setCellValue(item.getGongChengLiang() != null ? item.getGongChengLiang() : 0);
                createCellAddStype(row, 8, cellStyle).setCellValue(item.getZhuZhai() != null ? item.getZhuZhai() : 0);
                createCellAddStype(row, 9, cellStyle).setCellValue(item.getFeiJu() != null ? item.getFeiJu() : 0);
                createCellAddStype(row, 10, cellStyle).setCellValue(item.getShangYe() != null ? item.getShangYe() : 0);
                createCellAddStype(row, 11, cellStyle).setCellValue(item.getChongDianZhuang() != null ? item.getChongDianZhuang() : 0);
                createCellAddStype(row, 12, cellStyle).setCellValue(item.getRenGongCost() != null ? item.getRenGongCost().doubleValue() : 0);
                createCellAddStype(row, 13, cellStyle).setCellValue(item.getZhuCaiCost() != null ? item.getZhuCaiCost().doubleValue() : 0);
                createCellAddStype(row, 14, cellStyle).setCellValue(item.getFuCaiCost() != null ? item.getFuCaiCost().doubleValue() : 0);
                createCellAddStype(row, 15, cellStyle).setCellValue(item.getJiXieCost() != null ? item.getJiXieCost().doubleValue() : 0);
                createCellAddStype(row, 16, cellStyle).setCellValue(item.getQiYeCost() != null ? item.getQiYeCost().doubleValue() : 0);
                createCellAddStype(row, 17, cellStyle).setCellValue(item.getShuiJin() != null ? item.getShuiJin().doubleValue() : 0);
                createCellAddStype(row, 18, cellStyle).setCellValue(item.getComprehensivePrice() != null ? item.getComprehensivePrice().doubleValue() : 0);
                createCellAddStype(row, 19, cellStyle).setCellValue(item.getTotalPrice() != null ? item.getTotalPrice().doubleValue() : 0);
            }
            rowIndex++;
        }
        return workbook;
    }


    /**
     * 根据值获取名称
     * @param fenLei
     * @return
     */
    public String getFenLeiName(Integer fenLei) {
        if (fenLei == null) {
            return "未知";
        }
        switch (fenLei) {
            case 1:
                return "直流屏";
            case 2:
                return "高压开关柜";
            case 3:
                return "低压开关柜";
            case 4:
                return "母线槽";
            case 5:
                return "变压器";
            case 6:
                return "10kv高压电缆";
            case 7:
                return "架空线";
            case 8:
                return "低压电缆";
            case 9:
                return "预分支电缆";
            case 10:
                return "电表箱";
            case 11:
                return "配电箱";
            case 12:
                return "电表";
            case 13:
                return "高压桥架";
            case 14:
                return "调试";
            case 15:
                return "电缆通道";
            case 16:
                return "电杆";
            case 17:
                return "手续费";
            case 18:
                return "措施费";
            case 19:
                return "其他";
        }
        return "未知";
    }


    /**
     * 计算或提取基础信息
     * @param innerBidBaseTabulations
     * @param bidType
     * @return
     */
    public Map<String, Object> getInformation(List<InnerBidBaseTabulation> innerBidBaseTabulations, Integer bidType){
        Map<String, Object> tempMap = new HashMap<>();
        BigDecimal comprehensivePriceCount = BigDecimal.ZERO;
        BigDecimal totalPriceCount = BigDecimal.ZERO;
        for (InnerBidBaseTabulation tabulation : innerBidBaseTabulations){
            if (tabulation.getComprehensivePrice() != null){
                comprehensivePriceCount = comprehensivePriceCount.add(tabulation.getComprehensivePrice());
            }
            if (tabulation.getTotalPrice() != null){
                totalPriceCount = totalPriceCount.add(tabulation.getTotalPrice());
            }
        }
        if (!innerBidBaseTabulations.isEmpty()) {
            tempMap.put("project", innerBidBaseTabulations.get(0).getProject());
        }
        tempMap.put("bidType", bidType);
        tempMap.put("comprehensivePriceCount", comprehensivePriceCount);
        tempMap.put("totalPriceCount", totalPriceCount);
        return tempMap;
    }

    /**
     * 计算工程量、税金、合价等
     * @param tabulation
     * @return
     */
    public InnerBidBaseTabulation calculation(InnerBidBaseTabulation tabulation){
        BigDecimal gongChengLiang = BigDecimal.ZERO;
        BigDecimal qiYe = BigDecimal.ZERO;
        BigDecimal shuiJin = BigDecimal.ZERO;
        BigDecimal comprehensivePrice = BigDecimal.ZERO;
        BigDecimal totalPrice = BigDecimal.ZERO;

        Double zhuZhai = tabulation.getZhuZhai() != null ? tabulation.getZhuZhai() : 0;
        Double feiJu = tabulation.getFeiJu() != null ? tabulation.getFeiJu() : 0;
        Double shangYe = tabulation.getShangYe() != null ? tabulation.getShangYe() : 0;
        Double chongDianZhuang = tabulation.getChongDianZhuang() != null ? tabulation.getChongDianZhuang() : 0;

        BigDecimal renGongCost = tabulation.getRenGongCost() != null ? tabulation.getRenGongCost() : BigDecimal.ZERO;
        BigDecimal zhuCaiCost = tabulation.getZhuCaiCost() != null ? tabulation.getZhuCaiCost() : BigDecimal.ZERO;
        BigDecimal fuCaiCost = tabulation.getFuCaiCost() != null ? tabulation.getFuCaiCost() : BigDecimal.ZERO;
        BigDecimal jiXieCost = tabulation.getJiXieCost() != null ? tabulation.getJiXieCost() : BigDecimal.ZERO;
        BigDecimal qiYeCost = tabulation.getQiYeCost() != null ? tabulation.getQiYeCost() : BigDecimal.ZERO;
//        BigDecimal shuiJinCost = tabulation.getShuiJin() != null ? tabulation.getShuiJin() : BigDecimal.ZERO;

        BigDecimal qiYeCostFillIn = tabulation.getQiYeCostFillIn() != null ? tabulation.getQiYeCostFillIn() : BigDecimal.ZERO;
        BigDecimal shuiJinFillIn = tabulation.getShuiJinFillIn() != null ? tabulation.getShuiJinFillIn() : BigDecimal.ZERO;

        //企业管理
//        qiYe = renGongCost.add(zhuCaiCost).add(fuCaiCost).add(jiXieCost).multiply(qiYeCostFillIn);
        qiYe = qiYeCost;
        // 计算工程量
        gongChengLiang = BigDecimal.valueOf(zhuZhai+feiJu+shangYe+chongDianZhuang);
        // 计算税金
        shuiJin = renGongCost.add(zhuCaiCost).add(fuCaiCost).add(jiXieCost).add(qiYe).multiply(shuiJinFillIn);
        // 计算综合单价
        comprehensivePrice = renGongCost.add(zhuCaiCost).add(fuCaiCost).add(jiXieCost).add(qiYe).add(shuiJin);
        // 计算总价
        totalPrice = comprehensivePrice.multiply(gongChengLiang);

        tabulation.setGongChengLiang(gongChengLiang.doubleValue());
        tabulation.setShuiJin(shuiJin);
        tabulation.setComprehensivePrice(comprehensivePrice);
        tabulation.setTotalPrice(totalPrice);
        return tabulation;
    }


    /**
     * 分项总计
     * @param tabulationList
     * @return
     */
    public InnerBidBaseTabulation calculationSubtotal(List<InnerBidBaseTabulation> tabulationList){
        InnerBidBaseTabulation newT = new InnerBidBaseTabulation();
        BigDecimal renGongCostCount = BigDecimal.ZERO;
        BigDecimal zhuCaiCostCount = BigDecimal.ZERO;
        BigDecimal fuCaiCostCount = BigDecimal.ZERO;
        BigDecimal jiXieCostCount = BigDecimal.ZERO;
        BigDecimal qiYeCostCount = BigDecimal.ZERO;
        BigDecimal shuiJinCount = BigDecimal.ZERO;
        BigDecimal totalPrice = BigDecimal.ZERO;
        for (InnerBidBaseTabulation tabulation : tabulationList){
            BigDecimal gongChengLiang = tabulation.getGongChengLiang() != null ? BigDecimal.valueOf(tabulation.getGongChengLiang()) : BigDecimal.ZERO;
            BigDecimal renGongCost = tabulation.getRenGongCost() != null ? tabulation.getRenGongCost() : BigDecimal.ZERO;
            BigDecimal zhuCaiCost = tabulation.getZhuCaiCost() != null ? tabulation.getZhuCaiCost() : BigDecimal.ZERO;
            BigDecimal fuCaiCost = tabulation.getFuCaiCost() != null ? tabulation.getFuCaiCost() : BigDecimal.ZERO;
            BigDecimal jiXieCost = tabulation.getJiXieCost() != null ? tabulation.getJiXieCost() : BigDecimal.ZERO;
            BigDecimal qiYeCost = tabulation.getQiYeCost() != null ? tabulation.getQiYeCost() : BigDecimal.ZERO;
            BigDecimal shuiJin = tabulation.getShuiJin() != null ? tabulation.getShuiJin() : BigDecimal.ZERO;

            renGongCostCount = renGongCostCount.add(gongChengLiang.multiply(renGongCost));
            zhuCaiCostCount = zhuCaiCostCount.add(gongChengLiang.multiply(zhuCaiCost));
            fuCaiCostCount = fuCaiCostCount.add(gongChengLiang.multiply(fuCaiCost));
            jiXieCostCount = jiXieCostCount.add(gongChengLiang.multiply(jiXieCost));
            qiYeCostCount = qiYeCostCount.add(gongChengLiang.multiply(qiYeCost));
            shuiJinCount = shuiJinCount.add(gongChengLiang.multiply(shuiJin));
            totalPrice = totalPrice.add(tabulation.getTotalPrice());
        }

        newT.setName(tabulationList.get(0).getNameType1());
        newT.setNameType2("增补清单");
        newT.setRenGongCost(renGongCostCount);
        newT.setZhuCaiCost(zhuCaiCostCount);
        newT.setFuCaiCost(fuCaiCostCount);
        newT.setJiXieCost(jiXieCostCount);
        newT.setQiYeCost(qiYeCostCount);
        newT.setShuiJin(shuiJinCount);
        newT.setTotalPrice(totalPrice);

        return newT;
    }

}
